﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;

namespace XM.Comm
{
    public class NPOIExcelHelper : IDisposable
    {
        // 文件名
        private string FileName = null;
        // 文件资源
        private FileStream Stream = null;
        // 资源是否释放
        private bool Disposed = false;

        public NPOIExcelHelper(string fileName)
        {
            this.FileName = fileName;
            Disposed = false;
        }

        /// <summary>
        /// 将DataTable数据写入到Excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <param name="isColName">是否要导入列名</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int WriteExcel(DataTable data, string sheetName, bool isColName)
        {
            int count = 0;

            Stream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);

            IWorkbook Workbook = null;
            if (FileName.IndexOf(".xlsx") > 0) // 2007版本
                Workbook = new XSSFWorkbook();
            else if (FileName.IndexOf(".xls") > 0) // 2003版本
                Workbook = new HSSFWorkbook();

            if (Workbook == null)  // 
            {
                return -1;
            }

            var sheet = Workbook.CreateSheet(sheetName);

            if (isColName == true)  // 写入DataTable的列名
            {
                var row = sheet.CreateRow(0);
                for (int c = 0, l = data.Columns.Count; c < l; ++c)
                {
                    row.CreateCell(c).SetCellValue(data.Columns[c].ColumnName);
                }
                count = 1;
            }

            for (int i = 0, l = data.Rows.Count; i < l; ++i)
            {
                var row = sheet.CreateRow(count);
                for (int c = 0, len = data.Columns.Count; c < len; ++c)
                {
                    row.CreateCell(c).SetCellValue(data.Rows[i][c].ToString());
                }
                ++count;
            }

            Workbook.Write(Stream);  // 写入到excel

            return count;
        }

        /// <summary>
        /// 将Excel中的数据读入到DataTable中(无合并单元格处理)
        /// </summary>
        /// <param name="sheetName">要读取的sheet工作薄名称</param>
        /// <param name="fromRow">开始读取的行数(从0开始)</param>
        /// <param name="fromColName">是否从列名行开始</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ReadExcel(string sheetName, int fromRow, bool fromColName)
        {
            DataTable data = new DataTable();

            Stream = new FileStream(FileName, FileMode.Open, FileAccess.Read);

            IWorkbook Workbook = null;
            if (FileName.IndexOf(".xlsx") > 0) // 2007版本
                Workbook = new XSSFWorkbook(Stream);
            else if (FileName.IndexOf(".xls") > 0) // 2003版本
                Workbook = new HSSFWorkbook(Stream);

            var sheet = Workbook.GetSheet(sheetName);
            if (sheet == null)  // 如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
            {
                sheet = Workbook.GetSheetAt(0);
            }

            if (sheet != null)
            {
                var startRow = fromRow;  // 开始读取数据的行号
                var firstRow = sheet.GetRow(fromRow);
                int cellCount = firstRow.LastCellNum; // 一行最后一个cell的编号 即总的列数

                if (fromColName)  // 包括列名
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            var cellValue = cell.StringCellValue;
                            if (cellValue != null)  // 可以是空字符
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    startRow = fromRow + 1;
                }
                // TODO 原表无表头，需要自行创建

                // 最后一列的标号
                for (int i = startRow; i <= sheet.LastRowNum; ++i)
                {
                    var row = sheet.GetRow(i);
                    if (row == null) continue;  // 没有数据的行默认是null　　　　　　　

                    var dataRow = data.NewRow();
                    for (int c = row.FirstCellNum; c < cellCount; ++c)
                    {
                        if (row.GetCell(c) != null)  // 同理，没有数据的单元格都默认是null
                            dataRow[c] = row.GetCell(c).ToString();
                    }
                    data.Rows.Add(dataRow);
                }
            }

            return data;
        }


        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing">关闭文件占用资源</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!this.Disposed)
            {
                if (disposing)
                {
                    if (Stream != null)
                        Stream.Close();
                }

                Stream = null;
                Disposed = true;
            }
        }
    }
}
